/*
 * This file is released under the terms of the Artistic License.  Please see
 * the file LICENSE, included in this package, for details.
 *
 * Copyright (C) 2006 Rilson Nascimento
 *
 * Security Detail transaction
 * -------------------------
 * This transaction returns all available information related to a given
 * security.
 * 
 *
 * Based on TPC-E Standard Specification Draft Revision 0.32.2e Clause 3.3.8.
 */

/*
 * Frame 1
 * Get all details about the security
 */

CREATE OR REPLACE FUNCTION SecurityDetailFrame1 (
						IN access_lob_flag	BOOL,
						IN max_rows_to_return	INTEGER,
						IN start_day		DATE,
						IN symbol		VARCHAR,
                        OUT x52_wk_high NUMERIC,
                        OUT x52_wk_high_date DATE,
                        OUT x52_wk_low NUMERIC,
                        OUT x52_wk_low_date DATE,
                        OUT ceo_name VARCHAR,
                        OUT co_ad_ctry VARCHAR,
                        OUT co_ad_div VARCHAR,
                        OUT co_ad_line1 VARCHAR,
                        OUT co_ad_line2 VARCHAR,
                        OUT co_ad_town VARCHAR,
                        OUT co_ad_zip VARCHAR,
                        OUT co_desc VARCHAR,
                        OUT co_name VARCHAR,
                        OUT co_st_id VARCHAR,
                        OUT cp_co_name VARCHAR[],
                        OUT cp_in_name VARCHAR[],
                        OUT day VARCHAR,
                        OUT day_len INTEGER,
                        OUT divid NUMERIC,
                        OUT ex_ad_ctry VARCHAR,
                        OUT ex_ad_div VARCHAR,
                        OUT ex_ad_line1 VARCHAR,
                        OUT ex_ad_line2 VARCHAR,
                        OUT ex_ad_town VARCHAR,
                        OUT ex_ad_zip VARCHAR,
                        OUT ex_close INTEGER,
                        OUT ex_date DATE,
                        OUT ex_desc VARCHAR,
                        OUT ex_name VARCHAR,
                        OUT ex_num_symb INTEGER,
                        OUT ex_open INTEGER,
                        OUT fin VARCHAR,
                        OUT fin_len INTEGER,
                        OUT last_open NUMERIC,
                        OUT last_price NUMERIC,
                        OUT last_vol INTEGER,
                        OUT news VARCHAR,
                        OUT news_len INTEGER,
                        OUT num_out BIGINT,
                        OUT open_date DATE,
                        OUT pe_ratio NUMERIC,
                        OUT s_name VARCHAR,
                        OUT sp_rate VARCHAR,
                        OUT start_date DATE,
                        OUT yield NUMERIC) RETURNS SETOF record AS $$
DECLARE
	-- variables
	comp_id		BIGINT;
	rs		    RECORD;
	i		    INTEGER;
    fin_row     VARCHAR;
    day_row     VARCHAR;
    news_row    VARCHAR;
BEGIN
	SELECT	
		SEC.S_NAME,
		COM.CO_ID,
		COM.CO_NAME,
		COM.CO_SP_RATE,
		COM.CO_CEO,
		COM.CO_DESC,
        COM.CO_OPEN_DATE,
		COM.CO_ST_ID,
		CA.AD_LINE1,
		CA.AD_LINE2,
		ZCA.ZC_TOWN,
		ZCA.ZC_DIV,
		CA.AD_ZC_CODE,
		CA.AD_CTRY,
		S_NUM_OUT,
        S_START_DATE,
        S_EXCH_DATE,
		S_PE,
		S_52WK_HIGH,
        S_52WK_HIGH_DATE,
		S_52WK_LOW,
        S_52WK_LOW_DATE,
		S_DIVIDEND,
		S_YIELD,
		ZEA.ZC_DIV,
		EA.AD_CTRY,
		EA.AD_LINE1,
		EA.AD_LINE2,
		ZEA.ZC_TOWN,
		EA.AD_ZC_CODE,
		EX.EX_CLOSE,
		EX.EX_DESC,
		EX.EX_NAME,
		EX.EX_NUM_SYMB,
		EX.EX_OPEN
	INTO
        s_name,
        comp_id,
        co_name,
        sp_rate,
        ceo_name,
        co_desc,
        open_date,
        co_st_id,
        co_ad_line1,
        co_ad_line2,
        co_ad_town,
        co_ad_div,
        co_ad_zip,
        co_ad_ctry,
        num_out,
        start_date,
        ex_date,
        pe_ratio,
        x52_wk_high,
        x52_wk_high_date,
        x52_wk_low,
        x52_wk_low_date,
        divid,
        yield,
        ex_ad_div,
        ex_ad_ctry,
        ex_ad_line1,
        ex_ad_line2,
        ex_ad_town,
        ex_ad_zip,
        ex_close,
        ex_desc,
        ex_name,
        ex_num_symb,
        ex_open
	FROM	
        SECURITY SEC,
		COMPANY COM,
		ADDRESS CA,
		ADDRESS EA,
		ZIP_CODE ZCA,
		ZIP_CODE ZEA,
		EXCHANGE EX
	WHERE	
        SEC.S_SYMB = symbol AND
		COM.CO_ID = SEC.S_CO_ID AND
		CA.AD_ID = COM.CO_AD_ID AND
		EA.AD_ID = EX.EX_AD_ID AND
		EX.EX_ID = SEC.S_EX_ID AND
		ca.ad_zc_code = zca.zc_code AND
		ea.ad_zc_code = zea.zc_code;

	-- Should return max_comp_len(3) rows

	FOR rs IN
		SELECT COM.CO_NAME,
			   IND.IN_NAME
		FROM 
             COMPANY_COMPETITOR CCP,
			 COMPANY COM,
             INDUSTRY IND
		WHERE CCP.CP_CO_ID = comp_id AND
			  COM.CO_ID = CCP.CP_COMP_CO_ID AND
			  IND.IN_ID = CCP.CP_IN_ID
		LIMIT 3
	LOOP
		cp_co_name = cp_co_name || rs.CO_NAME;
		cp_in_name = cp_in_name || rs.IN_NAME;
	END LOOP;

	-- Should return max_fin_len(20) rows
	fin = '{';
	i = 0;
	FOR rs IN
		SELECT	FI_YEAR,
			    FI_QTR,
                FI_QTR_START_DATE,
			    FI_REVENUE,
			    FI_NET_EARN,
			    FI_BASIC_EPS,
			    FI_DILUT_EPS,
			    FI_MARGIN,
			    FI_INVENTORY,
			    FI_ASSETS,
			    FI_LIABILITY,
			    FI_OUT_BASIC,
			    FI_OUT_DILUT
		FROM	FINANCIAL
		WHERE	FI_CO_ID = comp_id
		ORDER BY FI_YEAR asc, FI_QTR
		LIMIT 20
	LOOP
        IF i > 0 THEN
            fin_row = ',{';
        ELSE
            fin_row = '{';
        END IF;
		fin_row = fin_row || rs.FI_YEAR;
		fin_row = fin_row || ',' || rs.FI_QTR;
		fin_row = fin_row || ',' || rs.FI_QTR_START_DATE;
		fin_row = fin_row || ',' || rs.FI_REVENUE;
		fin_row = fin_row || ',' || rs.FI_NET_EARN;
		fin_row = fin_row || ',' || rs.FI_BASIC_EPS;
		fin_row = fin_row || ',' || rs.FI_DILUT_EPS;
		fin_row = fin_row || ',' || rs.FI_MARGIN;
		fin_row = fin_row || ',' || rs.FI_INVENTORY;
		fin_row = fin_row || ',' || rs.FI_ASSETS;
		fin_row = fin_row || ',' || rs.FI_LIABILITY;
		fin_row = fin_row || ',' || rs.FI_OUT_BASIC;
		fin_row = fin_row || ',' || rs.FI_OUT_DILUT;
        fin_row = fin_row || '}';
        fin = fin || fin_row;
		i = i + 1;
	END LOOP;

    fin = fin || '}';
 
	fin_len = i;

	-- Should return max_rows_to_return rows
	-- max_rows_to_return is between 5 and 20
    day = '{';
	i = 0;
	FOR rs IN
		SELECT	
            DM_DATE,
			DM_CLOSE,
			DM_HIGH,
			DM_LOW,
			DM_VOL
		FROM
            DAILY_MARKET
		WHERE
            DM_S_SYMB = symbol AND
			DM_DATE >= start_day
		ORDER BY DM_DATE asc
		LIMIT max_rows_to_return
	LOOP
        IF i > 0 THEN
            day_row = ',{';
        ELSE
            day_row = '{';
        END IF;
        day_row = day_row || rs.DM_DATE;
		day_row = day_row || ',' ||rs.DM_CLOSE;
		day_row = day_row || ',' ||rs.DM_HIGH;
		day_row = day_row || ',' ||rs.DM_LOW;
		day_row = day_row || ',' ||rs.DM_VOL;
        day_row = day_row || '}';
        day = day || day_row;
		i = i + 1;
	END LOOP;

    day = day || '}';

	day_len = i;

	SELECT	LT_PRICE,
		    LT_OPEN_PRICE,
		    LT_VOL
	INTO
            last_price,
		    last_open,
		    last_vol
	FROM
            LAST_TRADE
	WHERE	
            LT_S_SYMB = symbol;
	
	-- Should return max_news_len (2) rows
	
	i = 0;
    news = '{';
	IF access_lob_flag THEN
		FOR rs IN
			SELECT	NI_ITEM,
				    NI_DTS,
				    NI_SOURCE,
				    NI_AUTHOR
			FROM	
                NEWS_XREF,
				NEWS_ITEM
			WHERE	
                NI_ID = NX_NI_ID AND
				NX_CO_ID = comp_id
			LIMIT 2
		LOOP
            if i > 0 THEN
                news_row = ',{';
            ELSE
                news_row = '{';
            END IF;
			news_row = news_row || '"' || rs.NI_ITEM || '"';
			news_row = news_row || ',"' || rs.NI_DTS || '"';
			news_row = news_row || ',' || rs.NI_SOURCE;
			news_row = news_row || ',' || rs.NI_AUTHOR;
			news_row = news_row || ',' || '""';
			news_row = news_row || ',' || '""';
            news_row = news_row || '}';
            news = news || news_row;
			i = i + 1;
		END LOOP;
	ELSE
		FOR rs IN
			SELECT
                NI_DTS,
				NI_SOURCE,
				NI_AUTHOR,
				NI_HEADLINE,
				NI_SUMMARY
			FROM	
                NEWS_XREF,
				NEWS_ITEM
			WHERE	
                NI_ID = NX_NI_ID AND
				NX_CO_ID = comp_id
			LIMIT 2
		LOOP
            if i > 0 THEN
                news_row = ',{';
            ELSE
                news_row = '{';
            END IF;
			news_row = news_row || '""';
			news_row = news_row || ',"' || rs.NI_DTS || '"';
			news_row = news_row || ',' || rs.NI_SOURCE;
			news_row = news_row || ',' || rs.NI_AUTHOR;
			news_row = news_row || ',"' || rs.NI_HEADLINE || '"';
			news_row = news_row || ',"' || rs.NI_SUMMARY || '"';
            news_row = news_row || '}';
            news = news || news_row;
			i = i + 1;
		END LOOP;
	END IF;
	
    news = news || '}';

	news_len = i;


	RETURN NEXT;
END;
$$ LANGUAGE 'plpgsql';
